{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据预览"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import sys \n",
    "from sklearn.model_selection import KFold\n",
    "%matplotlib inline\n",
    "# %matplotlib notebook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>target</th>\n",
       "      <th>ps_ind_01</th>\n",
       "      <th>ps_ind_02_cat</th>\n",
       "      <th>ps_ind_03</th>\n",
       "      <th>ps_ind_04_cat</th>\n",
       "      <th>ps_ind_05_cat</th>\n",
       "      <th>ps_ind_06_bin</th>\n",
       "      <th>ps_ind_07_bin</th>\n",
       "      <th>ps_ind_08_bin</th>\n",
       "      <th>...</th>\n",
       "      <th>ps_calc_11</th>\n",
       "      <th>ps_calc_12</th>\n",
       "      <th>ps_calc_13</th>\n",
       "      <th>ps_calc_14</th>\n",
       "      <th>ps_calc_15_bin</th>\n",
       "      <th>ps_calc_16_bin</th>\n",
       "      <th>ps_calc_17_bin</th>\n",
       "      <th>ps_calc_18_bin</th>\n",
       "      <th>ps_calc_19_bin</th>\n",
       "      <th>ps_calc_20_bin</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 59 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   id  target  ps_ind_01  ps_ind_02_cat  ps_ind_03  ps_ind_04_cat  \\\n",
       "0   7       0          2              2          5              1   \n",
       "1   9       0          1              1          7              0   \n",
       "2  13       0          5              4          9              1   \n",
       "3  16       0          0              1          2              0   \n",
       "4  17       0          0              2          0              1   \n",
       "\n",
       "   ps_ind_05_cat  ps_ind_06_bin  ps_ind_07_bin  ps_ind_08_bin       ...        \\\n",
       "0              0              0              1              0       ...         \n",
       "1              0              0              0              1       ...         \n",
       "2              0              0              0              1       ...         \n",
       "3              0              1              0              0       ...         \n",
       "4              0              1              0              0       ...         \n",
       "\n",
       "   ps_calc_11  ps_calc_12  ps_calc_13  ps_calc_14  ps_calc_15_bin  \\\n",
       "0           9           1           5           8               0   \n",
       "1           3           1           1           9               0   \n",
       "2           4           2           7           7               0   \n",
       "3           2           2           4           9               0   \n",
       "4           3           1           1           3               0   \n",
       "\n",
       "   ps_calc_16_bin  ps_calc_17_bin  ps_calc_18_bin  ps_calc_19_bin  \\\n",
       "0               1               1               0               0   \n",
       "1               1               1               0               1   \n",
       "2               1               1               0               1   \n",
       "3               0               0               0               0   \n",
       "4               0               0               1               1   \n",
       "\n",
       "   ps_calc_20_bin  \n",
       "0               1  \n",
       "1               0  \n",
       "2               0  \n",
       "3               0  \n",
       "4               0  \n",
       "\n",
       "[5 rows x 59 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    ".\\# 解决matplotlib显示中文问题\n",
    "# 仅适用于Windows\n",
    "plt.rcParams['font.sans-serif'] = ['SimHei']  # 指定默认字体\n",
    "plt.rcParams['axes.unicode_minus'] = False  # 解决保存图像是负号'-'显示为方块的问题\n",
    "test=pd.read_csv(r'.\\\\test.csv')\n",
    "train=pd.read_csv(r'.\\\\train.csv')\n",
    "sample=pd.read_csv(r'.\\\\sample_submission.csv')\n",
    "test_id=test['id']\n",
    "combine=[train,test]\n",
    "train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 595212 entries, 0 to 595211\n",
      "Data columns (total 59 columns):\n",
      "id                595212 non-null int64\n",
      "target            595212 non-null int64\n",
      "ps_ind_01         595212 non-null int64\n",
      "ps_ind_02_cat     595212 non-null int64\n",
      "ps_ind_03         595212 non-null int64\n",
      "ps_ind_04_cat     595212 non-null int64\n",
      "ps_ind_05_cat     595212 non-null int64\n",
      "ps_ind_06_bin     595212 non-null int64\n",
      "ps_ind_07_bin     595212 non-null int64\n",
      "ps_ind_08_bin     595212 non-null int64\n",
      "ps_ind_09_bin     595212 non-null int64\n",
      "ps_ind_10_bin     595212 non-null int64\n",
      "ps_ind_11_bin     595212 non-null int64\n",
      "ps_ind_12_bin     595212 non-null int64\n",
      "ps_ind_13_bin     595212 non-null int64\n",
      "ps_ind_14         595212 non-null int64\n",
      "ps_ind_15         595212 non-null int64\n",
      "ps_ind_16_bin     595212 non-null int64\n",
      "ps_ind_17_bin     595212 non-null int64\n",
      "ps_ind_18_bin     595212 non-null int64\n",
      "ps_reg_01         595212 non-null float64\n",
      "ps_reg_02         595212 non-null float64\n",
      "ps_reg_03         595212 non-null float64\n",
      "ps_car_01_cat     595212 non-null int64\n",
      "ps_car_02_cat     595212 non-null int64\n",
      "ps_car_03_cat     595212 non-null int64\n",
      "ps_car_04_cat     595212 non-null int64\n",
      "ps_car_05_cat     595212 non-null int64\n",
      "ps_car_06_cat     595212 non-null int64\n",
      "ps_car_07_cat     595212 non-null int64\n",
      "ps_car_08_cat     595212 non-null int64\n",
      "ps_car_09_cat     595212 non-null int64\n",
      "ps_car_10_cat     595212 non-null int64\n",
      "ps_car_11_cat     595212 non-null int64\n",
      "ps_car_11         595212 non-null int64\n",
      "ps_car_12         595212 non-null float64\n",
      "ps_car_13         595212 non-null float64\n",
      "ps_car_14         595212 non-null float64\n",
      "ps_car_15         595212 non-null float64\n",
      "ps_calc_01        595212 non-null float64\n",
      "ps_calc_02        595212 non-null float64\n",
      "ps_calc_03        595212 non-null float64\n",
      "ps_calc_04        595212 non-null int64\n",
      "ps_calc_05        595212 non-null int64\n",
      "ps_calc_06        595212 non-null int64\n",
      "ps_calc_07        595212 non-null int64\n",
      "ps_calc_08        595212 non-null int64\n",
      "ps_calc_09        595212 non-null int64\n",
      "ps_calc_10        595212 non-null int64\n",
      "ps_calc_11        595212 non-null int64\n",
      "ps_calc_12        595212 non-null int64\n",
      "ps_calc_13        595212 non-null int64\n",
      "ps_calc_14        595212 non-null int64\n",
      "ps_calc_15_bin    595212 non-null int64\n",
      "ps_calc_16_bin    595212 non-null int64\n",
      "ps_calc_17_bin    595212 non-null int64\n",
      "ps_calc_18_bin    595212 non-null int64\n",
      "ps_calc_19_bin    595212 non-null int64\n",
      "ps_calc_20_bin    595212 non-null int64\n",
      "dtypes: float64(10), int64(49)\n",
      "memory usage: 267.9 MB\n"
     ]
    }
   ],
   "source": [
    "train.info()\n",
    "train.describe()\n",
    "ps_car_09_cat_train=train['ps_car_09_cat']\n",
    "ps_car_09_cat_test=test['ps_car_09_cat']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "col_calc=train.columns[train.columns.str[6]=='c']\n",
    "train.drop(col_calc,axis=1,inplace=True)\n",
    "test.drop(col_calc,axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "combine=[train,test]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "corr=train.corr()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 每列数据的集合和个数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "for data in combine:\n",
    "    data.drop(['id'],axis=1,inplace=True)\n",
    "# unique=pd.DataFrame(index=np.arange(0,70482,1),columns=col.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ps_ind_01            8\n",
      "ps_ind_02_cat        5\n",
      "ps_ind_03           12\n",
      "ps_ind_04_cat        3\n",
      "ps_ind_05_cat        8\n",
      "ps_ind_06_bin        2\n",
      "ps_ind_07_bin        2\n",
      "ps_ind_08_bin        2\n",
      "ps_ind_09_bin        2\n",
      "ps_ind_10_bin        2\n",
      "ps_ind_11_bin        2\n",
      "ps_ind_12_bin        2\n",
      "ps_ind_13_bin        2\n",
      "ps_ind_14            5\n",
      "ps_ind_15           14\n",
      "ps_ind_16_bin        2\n",
      "ps_ind_17_bin        2\n",
      "ps_ind_18_bin        2\n",
      "ps_reg_01           10\n",
      "ps_reg_02           19\n",
      "ps_reg_03         5013\n",
      "ps_car_01_cat       13\n",
      "ps_car_02_cat        3\n",
      "ps_car_03_cat        3\n",
      "ps_car_04_cat       10\n",
      "ps_car_05_cat        3\n",
      "ps_car_06_cat       18\n",
      "ps_car_07_cat        3\n",
      "ps_car_08_cat        2\n",
      "ps_car_09_cat        6\n",
      "ps_car_10_cat        3\n",
      "ps_car_11_cat      104\n",
      "ps_car_11            5\n",
      "ps_car_12          184\n",
      "ps_car_13        70482\n",
      "ps_car_14          850\n",
      "ps_car_15           15\n",
      "Name: Count, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "count=pd.DataFrame(columns=test.columns.values,index=['Count'])\n",
    "for data in test.columns.values:\n",
    "    count[data]=len(set(train[data].values))\n",
    "#     unique[data]=pd.Series(list(set(train[data].values)))\n",
    "# unique_count=pd.concat([count,unique])\n",
    "print(count.T['Count'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 有缺失值的列和缺失比例"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# for data in combine:\n",
    "#     data.drop(['ps_reg_03','ps_car_05_cat','ps_car_14','ps_car_07_cat'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "train[train==-1]=np.nan\n",
    "train_null=train.isnull().any()\n",
    "# train_null[train_null==True]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "test[test==-1]=np.nan\n",
    "test_null=test.isnull().any()\n",
    "# test_null[test_null==True]\n",
    "combine=[train,test]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ps_ind_02_cat</th>\n",
       "      <th>ps_ind_04_cat</th>\n",
       "      <th>ps_ind_05_cat</th>\n",
       "      <th>ps_reg_03</th>\n",
       "      <th>ps_car_01_cat</th>\n",
       "      <th>ps_car_02_cat</th>\n",
       "      <th>ps_car_03_cat</th>\n",
       "      <th>ps_car_05_cat</th>\n",
       "      <th>ps_car_07_cat</th>\n",
       "      <th>ps_car_09_cat</th>\n",
       "      <th>ps_car_11</th>\n",
       "      <th>ps_car_12</th>\n",
       "      <th>ps_car_14</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Train_Scale</th>\n",
       "      <td>0.000363</td>\n",
       "      <td>0.000139</td>\n",
       "      <td>0.00976</td>\n",
       "      <td>0.181065</td>\n",
       "      <td>0.00018</td>\n",
       "      <td>0.000008</td>\n",
       "      <td>0.690898</td>\n",
       "      <td>0.447825</td>\n",
       "      <td>0.019302</td>\n",
       "      <td>0.000956</td>\n",
       "      <td>0.000008</td>\n",
       "      <td>0.000002</td>\n",
       "      <td>0.071605</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             ps_ind_02_cat  ps_ind_04_cat  ps_ind_05_cat  ps_reg_03  \\\n",
       "Train_Scale       0.000363       0.000139        0.00976   0.181065   \n",
       "\n",
       "             ps_car_01_cat  ps_car_02_cat  ps_car_03_cat  ps_car_05_cat  \\\n",
       "Train_Scale        0.00018       0.000008       0.690898       0.447825   \n",
       "\n",
       "             ps_car_07_cat  ps_car_09_cat  ps_car_11  ps_car_12  ps_car_14  \n",
       "Train_Scale       0.019302       0.000956   0.000008   0.000002   0.071605  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_scale=pd.DataFrame(index=['Train_Scale'],columns=train_null[train_null==True].index)\n",
    "for index in train_null[train_null==True].index:\n",
    "    col_nu=train[index].isnull()\n",
    "    train_scale[index]=len(train[index][col_nu==True])/len(train[index])\n",
    "train_scale"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ps_ind_02_cat</th>\n",
       "      <th>ps_ind_04_cat</th>\n",
       "      <th>ps_ind_05_cat</th>\n",
       "      <th>ps_reg_03</th>\n",
       "      <th>ps_car_01_cat</th>\n",
       "      <th>ps_car_02_cat</th>\n",
       "      <th>ps_car_03_cat</th>\n",
       "      <th>ps_car_05_cat</th>\n",
       "      <th>ps_car_07_cat</th>\n",
       "      <th>ps_car_09_cat</th>\n",
       "      <th>ps_car_11</th>\n",
       "      <th>ps_car_14</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Test_Scale</th>\n",
       "      <td>0.000344</td>\n",
       "      <td>0.000162</td>\n",
       "      <td>0.009756</td>\n",
       "      <td>0.181094</td>\n",
       "      <td>0.000179</td>\n",
       "      <td>0.000006</td>\n",
       "      <td>0.690972</td>\n",
       "      <td>0.448423</td>\n",
       "      <td>0.019412</td>\n",
       "      <td>0.000982</td>\n",
       "      <td>0.000001</td>\n",
       "      <td>0.071465</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            ps_ind_02_cat  ps_ind_04_cat  ps_ind_05_cat  ps_reg_03  \\\n",
       "Test_Scale       0.000344       0.000162       0.009756   0.181094   \n",
       "\n",
       "            ps_car_01_cat  ps_car_02_cat  ps_car_03_cat  ps_car_05_cat  \\\n",
       "Test_Scale       0.000179       0.000006       0.690972       0.448423   \n",
       "\n",
       "            ps_car_07_cat  ps_car_09_cat  ps_car_11  ps_car_14  \n",
       "Test_Scale       0.019412       0.000982   0.000001   0.071465  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_scale=pd.DataFrame(index=['Test_Scale'],columns=test_null[test_null==True].index)\n",
    "for index in test_null[test_null==True].index:\n",
    "    col_nu=test[index].isnull()\n",
    "    test_scale[index]=len(test[index][col_nu==True])/len(test[index])\n",
    "test_scale"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 填充ps_car_09_cat列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ps_ind_01       -0.102582\n",
       "ps_car_01_cat    0.272900\n",
       "ps_car_03_cat    0.272407\n",
       "ps_car_05_cat   -0.327751\n",
       "ps_car_09_cat    1.000000\n",
       "ps_car_11        0.118969\n",
       "Name: ps_car_09_cat, dtype: float64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "corr[abs(corr['ps_car_09_cat'])>0.1]['ps_car_09_cat']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "guess_car09=[]\n",
    "len_guess=np.zeros((12))\n",
    "for data in combine:\n",
    "    for i in range(0,12):\n",
    "        guess=data[data['ps_car_01_cat']==i]['ps_car_09_cat'].dropna()\n",
    "        for j in range(0,5):                   #先根据数字和次数存储起来，找到列中出现最多次数的那个数字\n",
    "            len_guess[j]=len(guess[guess==j])\n",
    "        guess_car09.append(len_guess.argmax())\n",
    "    for i in range(0,12):\n",
    "        data.loc[(data['ps_car_09_cat'].isnull())&(data['ps_car_01_cat']==i),'ps_car_09_cat']=guess_car09[i]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "train['ps_car_09_cat'].fillna(2,inplace=True)\n",
    "train['ps_car_09_cat'] = train['ps_car_09_cat'].astype(int)\n",
    "test['ps_car_09_cat'].fillna(2,inplace=True)\n",
    "test['ps_car_09_cat'] = test['ps_car_09_cat'].astype(int)\n",
    "combine=[train,test]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 填充ps_car_03_cat列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ps_car_03_cat    1.000000\n",
       "ps_car_05_cat    0.489789\n",
       "ps_car_08_cat   -0.248662\n",
       "ps_car_09_cat    0.272407\n",
       "Name: ps_car_03_cat, dtype: float64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "corr[abs(corr['ps_car_03_cat'])>0.2]['ps_car_03_cat']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "guess_ps03 = np.zeros((2,5))\n",
    "for data in combine:\n",
    "    for i in range(0,2):\n",
    "        for j in range(0,5):\n",
    "            guess=data[(data['ps_car_08_cat']==i)&(data['ps_car_09_cat']==j)]['ps_car_03_cat'].dropna()\n",
    "            if len(guess[guess==0])>len(guess[guess==1]):#找到列中出现最多次数的那个数字\n",
    "                ps_car_03_cat_guess=0\n",
    "            else:\n",
    "                ps_car_03_cat_guess=1\n",
    "            guess_ps03[i][j]=ps_car_03_cat_guess\n",
    "            \n",
    "    for i in range(0,2):\n",
    "        for j in range(0,5):\n",
    "            data.loc[(data['ps_car_03_cat'].isnull())&(data['ps_car_08_cat']==i)&(data['ps_car_09_cat']==j),'ps_car_03_cat']=guess_ps03[i][j]\n",
    "combine=[train,test]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 填充ps_car_05_cat列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ps_ind_01        0.213009\n",
       "ps_car_03_cat    0.489789\n",
       "ps_car_05_cat    1.000000\n",
       "ps_car_09_cat   -0.327751\n",
       "Name: ps_car_05_cat, dtype: float64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "corr[abs(corr['ps_car_05_cat'])>0.2]['ps_car_05_cat']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "guess_ps05 = np.zeros((8,5))\n",
    "for data in combine:\n",
    "    for i in range(0,8):\n",
    "        for j in range(0,5):\n",
    "            guess=data[(data['ps_ind_01']==i)&(data['ps_car_09_cat']==j)]['ps_car_05_cat'].dropna()\n",
    "            if len(guess[guess==0])>len(guess[guess==1]):#找到列中出现最多次数的那个数字\n",
    "                ps_car_05_cat_guess=0\n",
    "            else:\n",
    "                ps_car_05_cat_guess=1\n",
    "            guess_ps05[i][j]=ps_car_05_cat_guess\n",
    "            \n",
    "    for i in range(0,8):\n",
    "        for j in range(0,5):\n",
    "            data.loc[(data['ps_car_05_cat'].isnull())&(data['ps_ind_01']==i)&(data['ps_car_09_cat']==j),'ps_car_05_cat']=guess_ps05[i][j]\n",
    "combine=[train,test]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 填充['ps_ind_04_cat']列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ps_ind_02_cat    0.147110\n",
       "ps_ind_04_cat    1.000000\n",
       "ps_ind_06_bin   -0.297737\n",
       "ps_ind_07_bin    0.304949\n",
       "Name: ps_ind_04_cat, dtype: float64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "corr[abs(corr['ps_ind_04_cat'])>0.1]['ps_ind_04_cat']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "guess_pic = np.zeros((2,2))\n",
    "for data in combine:\n",
    "    for i in range(0,2):\n",
    "        for j in range(0,2):\n",
    "            guess=data[(data['ps_ind_06_bin']==i)&(data['ps_ind_07_bin']==j)]['ps_ind_04_cat'].dropna()\n",
    "            if len(guess[guess==0])>len(guess[guess==1]):#找到列中出现最多次数的那个数字\n",
    "                ps_ind_04_cat_guess=0\n",
    "            else:\n",
    "                ps_ind_04_cat_guess=1\n",
    "            guess_pic[i][j]=ps_ind_04_cat_guess\n",
    "            \n",
    "    for i in range(0,2):\n",
    "        for j in range(0,2):\n",
    "            data.loc[(data['ps_ind_04_cat'].isnull())&(data['ps_ind_06_bin']==i)&(data['ps_ind_07_bin']==j),'ps_ind_04_cat']=guess_pic[i][j]\n",
    "    data['ps_ind_04_cat'] = data['ps_ind_04_cat'].astype(int)\n",
    "combine=[train,test]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 填充\tps_ind_02_cat\t列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ps_ind_02_cat    1.000000\n",
       "ps_ind_04_cat    0.147110\n",
       "ps_ind_06_bin   -0.125653\n",
       "ps_ind_09_bin    0.110076\n",
       "ps_car_12       -0.110247\n",
       "Name: ps_ind_02_cat, dtype: float64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "corr[abs(corr['ps_ind_02_cat'])>0.1]['ps_ind_02_cat']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([1, 0, 1, ..., 0, 1, 0])"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train['ps_ind_04_cat'].values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "guess_pi02 = np.zeros((2,2))\n",
    "len_guess=np.zeros((4))\n",
    "for data in combine:\n",
    "    for i in range(0,2):\n",
    "        for j in range(0,2):\n",
    "            guess=data[(data['ps_ind_04_cat']==i)&(data['ps_ind_06_bin']==j)]['ps_ind_02_cat'].dropna()\n",
    "            for n in range(0,4):                   #先根据数字和次数存储起来，找到列中出现最多次数的那个数字\n",
    "                len_guess[n]=len(guess[guess==n])\n",
    "            guess_pi02[i][j]=len_guess.argmax()+1\n",
    "            \n",
    "    for i in range(0,2):\n",
    "        for j in range(0,2):\n",
    "            data.loc[(data['ps_ind_02_cat'].isnull())&(data['ps_ind_04_cat']==i)&(data['ps_ind_06_bin']==j),'ps_ind_02_cat']=guess_pi02[i][j]\n",
    "combine=[train,test]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 填充ps_ind_05_cat列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ps_ind_03        0.053250\n",
       "ps_ind_05_cat    1.000000\n",
       "ps_car_07_cat    0.053777\n",
       "Name: ps_ind_05_cat, dtype: float64"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "corr[abs(corr['ps_ind_05_cat'])>0.05]['ps_ind_05_cat']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "guess_pi=[]\n",
    "len_guess=np.zeros((7))\n",
    "for data in combine:\n",
    "    for i in range(0,12):\n",
    "        guess=data[data['ps_ind_03']==i]['ps_ind_05_cat'].dropna()\n",
    "        for j in range(0,7):                   #先根据数字和次数存储起来，找到列中出现最多次数的那个数字\n",
    "            len_guess[j]=len(guess[guess==j])\n",
    "        guess_pi.append(len_guess.argmax())\n",
    "    for i in range(0,12):\n",
    "        data.loc[(data['ps_ind_05_cat'].isnull())&(data['ps_ind_03']==i),'ps_ind_05_cat']=guess_pi[i]\n",
    "    data['ps_ind_05_cat'] = data['ps_ind_05_cat'].astype(int)\n",
    "combine=[train,test]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 填充ps_reg_03\t列  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ps_ind_01        0.139125\n",
       "ps_ind_09_bin    0.133761\n",
       "ps_reg_01        0.637035\n",
       "ps_reg_02        0.516457\n",
       "ps_reg_03        1.000000\n",
       "ps_car_01_cat    0.230339\n",
       "ps_car_02_cat   -0.135690\n",
       "Name: ps_reg_03, dtype: float64"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "corr[abs(corr['ps_reg_03'])>0.1]['ps_reg_03']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#ps_reg_01和ps_reg_02，这里只填充了一部分数据，还有的交叉数据是全部为nan\n",
    "guess_ps_reg=np.zeros((10,19))\n",
    "for data in combine:\n",
    "    for i in range(0,10,1):\n",
    "        for j in range(0,19,1):\n",
    "            guess=data[(data['ps_reg_01']==i/10)&(data['ps_reg_02']==j/10)]['ps_reg_03'].dropna()\n",
    "            guess_ps_reg[i][j]=guess.median()#median()\n",
    "    for i in range(0,10,1):\n",
    "        for j in range(0,19,1):\n",
    "            data.loc[(data['ps_reg_01']==i/10)&(data['ps_reg_02']==j/10)&(data['ps_reg_03'].isnull()),'ps_reg_03']=guess_ps_reg[i][j]\n",
    "    data['ps_reg_03'] = data['ps_reg_03'].astype(float)\n",
    "combine=[train,test]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#ps_reg_01和ps_car_01_cat 填充剩下的一部分缺失值\n",
    "guess_ps_reg=np.zeros((10,12))\n",
    "for data in combine:\n",
    "    for i in range(0,10,1):\n",
    "        for j in range(0,12,1):\n",
    "            guess=data[(data['ps_reg_01']==i/10)&(data['ps_car_01_cat']==j)]['ps_reg_03'].dropna()\n",
    "            guess_ps_reg[i][j]=guess.median()#median()\n",
    "    for i in range(0,10,1):\n",
    "        for j in range(0,12,1):\n",
    "            data.loc[(data['ps_reg_01']==i/10)&(data['ps_car_01_cat']==j)&(data['ps_reg_03'].isnull()),'ps_reg_03']=guess_ps_reg[i][j]\n",
    "    data['ps_reg_03'] = data['ps_reg_03'].astype(float)\n",
    "combine=[train,test]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ps_reg_03_dur</th>\n",
       "      <th>target</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>(0.0573, 0.857]</td>\n",
       "      <td>0.031785</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>(0.857, 1.652]</td>\n",
       "      <td>0.043147</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>(1.652, 2.447]</td>\n",
       "      <td>0.052693</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>(2.447, 3.243]</td>\n",
       "      <td>0.063927</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>(3.243, 4.038]</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     ps_reg_03_dur    target\n",
       "0  (0.0573, 0.857]  0.031785\n",
       "1   (0.857, 1.652]  0.043147\n",
       "2   (1.652, 2.447]  0.052693\n",
       "3   (2.447, 3.243]  0.063927\n",
       "4   (3.243, 4.038]  0.000000"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train['ps_reg_03_dur']=pd.cut(train['ps_reg_03'],5)\n",
    "train[['ps_reg_03_dur','target']].groupby(['ps_reg_03_dur'],as_index=False).mean().sort_values(by='ps_reg_03_dur',ascending=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "for data in combine:\n",
    "    data.loc[data['ps_reg_03']<=0.857,'ps_reg_03']=0\n",
    "    data.loc[(data['ps_reg_03']>0.857)&(data['ps_reg_03']<=1.652),'ps_reg_03']=1\n",
    "    data.loc[(data['ps_reg_03']>1.652)&(data['ps_reg_03']<=2.447),'ps_reg_03']=2\n",
    "    data.loc[(data['ps_reg_03']>2.447)&(data['ps_reg_03']<=3.243),'ps_reg_03']=3\n",
    "    data.loc[data['ps_reg_03']>3.243,'ps_reg_03']=4\n",
    "#     data.loc[data['ps_reg_03']>3.375,'ps_reg_03']=5\n",
    "train.drop(['ps_reg_03_dur'],axis=1,inplace=True)\n",
    "combine=[train,test]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 处理缺失列 ps_car_01_cat\t"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ps_reg_01        0.184266\n",
       "ps_reg_02        0.231710\n",
       "ps_reg_03        0.230339\n",
       "ps_car_01_cat    1.000000\n",
       "ps_car_02_cat   -0.152733\n",
       "ps_car_03_cat    0.138055\n",
       "ps_car_05_cat   -0.147258\n",
       "ps_car_09_cat    0.272900\n",
       "ps_car_11        0.110921\n",
       "ps_car_12        0.125987\n",
       "ps_car_13        0.167127\n",
       "ps_car_15        0.102683\n",
       "Name: ps_car_01_cat, dtype: float64"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "corr[abs(corr['ps_car_01_cat'])>0.1]['ps_car_01_cat']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#ps_reg_02和ps_car_09_cat\n",
    "combine=[train,test]\n",
    "guess_ps_car=np.zeros((10,5))\n",
    "for data in combine:\n",
    "    for i in range(0,10,1):\n",
    "        for j in range(0,5,1):\n",
    "            len_guess=[]\n",
    "            guess=data[(data['ps_reg_01']==i/10)&(data['ps_car_09_cat']==j)]['ps_car_01_cat'].dropna()\n",
    "            for n in range(0,12,1):\n",
    "                len_guess.append(len(guess[guess==n]))\n",
    "            guess_ps_car[i][j]=np.array(len_guess).argmax()\n",
    "    for i in range(0,10,1):\n",
    "        for j in range(0,5,1):\n",
    "            data.loc[(data['ps_reg_01']==i/10)&(data['ps_car_09_cat']==j)&(data['ps_car_01_cat'].isnull()),'ps_car_01_cat']=guess_ps_car[i][j]\n",
    "    data['ps_car_01_cat'].fillna(11,inplace=True)\n",
    "combine=[train,test]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 填充ps_car_07_cat列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ps_car_02_cat    0.103632\n",
       "ps_car_03_cat   -0.148339\n",
       "ps_car_04_cat   -0.183374\n",
       "ps_car_07_cat    1.000000\n",
       "ps_car_12       -0.125777\n",
       "ps_car_13       -0.223412\n",
       "Name: ps_car_07_cat, dtype: float64"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "corr[abs(corr['ps_car_07_cat'])>0.1]['ps_car_07_cat']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "target           False\n",
       "ps_ind_01        False\n",
       "ps_ind_02_cat    False\n",
       "ps_ind_03        False\n",
       "ps_ind_04_cat    False\n",
       "ps_ind_05_cat    False\n",
       "ps_ind_06_bin    False\n",
       "ps_ind_07_bin    False\n",
       "ps_ind_08_bin    False\n",
       "ps_ind_09_bin    False\n",
       "ps_ind_10_bin    False\n",
       "ps_ind_11_bin    False\n",
       "ps_ind_12_bin    False\n",
       "ps_ind_13_bin    False\n",
       "ps_ind_14        False\n",
       "ps_ind_15        False\n",
       "ps_ind_16_bin    False\n",
       "ps_ind_17_bin    False\n",
       "ps_ind_18_bin    False\n",
       "ps_reg_01        False\n",
       "ps_reg_02        False\n",
       "ps_reg_03        False\n",
       "ps_car_01_cat    False\n",
       "ps_car_02_cat     True\n",
       "ps_car_03_cat    False\n",
       "ps_car_04_cat    False\n",
       "ps_car_05_cat    False\n",
       "ps_car_06_cat    False\n",
       "ps_car_07_cat    False\n",
       "ps_car_08_cat    False\n",
       "ps_car_09_cat    False\n",
       "ps_car_10_cat    False\n",
       "ps_car_11_cat    False\n",
       "ps_car_11         True\n",
       "ps_car_12         True\n",
       "ps_car_13        False\n",
       "ps_car_14         True\n",
       "ps_car_15        False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "guess_pcc=np.zeros((10))\n",
    "for data in combine:\n",
    "    for i in range(0,10,1):\n",
    "        guess=data[data['ps_car_04_cat']==i]['ps_car_07_cat'].dropna()\n",
    "        if len(guess[guess==0])>len(guess[guess==1]):#找到列中出现最多次数的那个数字\n",
    "            pcc_guess=0\n",
    "        else:\n",
    "            pcc_guess=1\n",
    "        guess_pcc[i]=pcc_guess\n",
    "            \n",
    "    for i in range(0,10,1):\n",
    "            data.loc[(data['ps_car_04_cat']==i)&(data['ps_car_07_cat'].isnull()),'ps_car_07_cat']=guess_pcc[i]\n",
    "    data['ps_car_07_cat'] = data['ps_car_07_cat'].astype(int)\n",
    "combine=[train,test]\n",
    "train.isnull().any()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 填充ps_car_14列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ps_car_03_cat   -0.163385\n",
       "ps_car_08_cat    0.160010\n",
       "ps_car_14        1.000000\n",
       "Name: ps_car_14, dtype: float64"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "corr[abs(corr['ps_car_14'])>0.1]['ps_car_14']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "guess_car14=[]\n",
    "for data in combine:\n",
    "    for i in range(0,2):\n",
    "        guess=data[data['ps_car_08_cat']==i]['ps_car_14'].dropna()\n",
    "        guess_car14.append(guess.median())\n",
    "    for i in range(0,2):\n",
    "        data.loc[(data['ps_car_14'].isnull())&(data['ps_car_08_cat']==i),'ps_car_14']=guess_car14[i]\n",
    "    data['ps_car_14'] = data['ps_car_14'].astype(float)\n",
    "combine=[train,test]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "### 删除训练集剩下很少的缺失值ps_car_02_cat和ps_car_12列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "train=train.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ps_ind_01        False\n",
       "ps_ind_02_cat    False\n",
       "ps_ind_03        False\n",
       "ps_ind_04_cat    False\n",
       "ps_ind_05_cat    False\n",
       "ps_ind_06_bin    False\n",
       "ps_ind_07_bin    False\n",
       "ps_ind_08_bin    False\n",
       "ps_ind_09_bin    False\n",
       "ps_ind_10_bin    False\n",
       "ps_ind_11_bin    False\n",
       "ps_ind_12_bin    False\n",
       "ps_ind_13_bin    False\n",
       "ps_ind_14        False\n",
       "ps_ind_15        False\n",
       "ps_ind_16_bin    False\n",
       "ps_ind_17_bin    False\n",
       "ps_ind_18_bin    False\n",
       "ps_reg_01        False\n",
       "ps_reg_02        False\n",
       "ps_reg_03        False\n",
       "ps_car_01_cat    False\n",
       "ps_car_02_cat     True\n",
       "ps_car_03_cat    False\n",
       "ps_car_04_cat    False\n",
       "ps_car_05_cat    False\n",
       "ps_car_06_cat    False\n",
       "ps_car_07_cat    False\n",
       "ps_car_08_cat    False\n",
       "ps_car_09_cat    False\n",
       "ps_car_10_cat    False\n",
       "ps_car_11_cat    False\n",
       "ps_car_11         True\n",
       "ps_car_12        False\n",
       "ps_car_13        False\n",
       "ps_car_14        False\n",
       "ps_car_15        False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test.isnull().any()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 填充验证集缺失值很少的ps_car_02_cat"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "guess=test['ps_car_02_cat'].dropna()\n",
    "len(guess[guess==0])>len(guess[guess==1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "test['ps_car_02_cat'].fillna(1,inplace=True)\n",
    "test['ps_car_11'].fillna(3,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "combine=[train,test]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 对连续值的列进行归类"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# cols_for_dur=['ps_ind_01','ps_reg_02','ps_car_01_cat','ps_reg_01','ps_ind_15',\n",
    "#               'ps_car_11_cat','ps_car_06_cat','ps_car_12','ps_car_15','ps_ind_03']\n",
    "# # ['ps_reg_02','ps_ind_15','ps_car_11_cat','ps_car_12','ps_car_15','ps_ind_03']\n",
    "# for col in cols_for_dur:\n",
    "#     train['new_col']=pd.cut(train[col],5)\n",
    "#     list_interval=list(set(train['new_col'].values))\n",
    "#     list_interval.sort()\n",
    "#     for data in combine:\n",
    "#         data.loc[data[col]<list_interval[0].right,col]=0\n",
    "#         data.loc[(data[col]>list_interval[1].left)&(data[col]<=list_interval[1].right),col]=1\n",
    "#         data.loc[(data[col]>list_interval[2].left)&(data[col]<=list_interval[2].right),col]=2\n",
    "#         data.loc[(data[col]>list_interval[3].left)&(data[col]<=list_interval[3].right),col]=3\n",
    "#         data.loc[data[col]>list_interval[4].left,col]=4\n",
    "#     train=train.drop(['new_col'],axis=1)\n",
    "#     combine=[train,test]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# test.head(100000).to_csv(r'c:\\Users\\ZouJiu\\Desktop\\front1.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "198"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#类别形整数变量转换为类别变量\n",
    "col_cato=train.columns[train.columns.str[10]=='c']\n",
    "def int_to_category(df):\n",
    "    for col in col_cato:\n",
    "        df[col]=df[col].astype('category')\n",
    "for data in combine:\n",
    "    data=int_to_category(data)\n",
    "combine=[train,test]\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "#类别形变量转换为指示性变量\n",
    "for col in col_cato:\n",
    "    new_dummies=pd.get_dummies(train[col],prefix=col)\n",
    "    train=pd.concat([train,new_dummies],axis=1)\n",
    "    train.drop([col],axis=1,inplace=True)\n",
    "for col in col_cato:\n",
    "    new_dummies=pd.get_dummies(test[col],prefix=col)\n",
    "    test=pd.concat([test,new_dummies],axis=1)\n",
    "    test.drop([col],axis=1,inplace=True)\n",
    "\n",
    "len(test.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 交叉验证建模"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "K=10\n",
    "from sklearn.model_selection import StratifiedKFold\n",
    "# kf = KFold(n_splits = K,random_state = 1,shuffle = True)\n",
    "kf=StratifiedKFold(n_splits = K,random_state = 90,shuffle = True)\n",
    "X=train.drop(['target'],axis=1)\n",
    "y=train['target']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def eval_gini(y_true, y_prob):\n",
    "    y_true = np.asarray(y_true)\n",
    "    y_true = y_true[np.argsort(y_prob)]\n",
    "    ntrue = 0\n",
    "    gini = 0\n",
    "    delta = 0\n",
    "    n = len(y_true)\n",
    "    for i in range(n-1, -1, -1):\n",
    "        y_i = y_true[i]\n",
    "        ntrue += y_i\n",
    "        gini += y_i * delta\n",
    "        delta += 1 - y_i\n",
    "    gini = 1 - 2 * gini / (ntrue * (n - ntrue))\n",
    "    return gini\n",
    "def normal_gini(y_true,y_prob):\n",
    "    return eval_gini(y_true,y_prob)/eval_gini(y_true,y_true)\n",
    "def xgb_gini(pred,dtrain):\n",
    "    label=dtrain.get_label()\n",
    "    return normal_gini(label,pred)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import xgboost as xgb\n",
    "#n=16 0.465 不离散化  不交叉验证 \n",
    "#2折交叉  reg_lambda=1.3  0.2756\n",
    "model=xgb.XGBClassifier(n_estimators=400,\n",
    "                        max_depth=6,#6\n",
    "                        objective=\"binary:logistic\",\n",
    "                        learning_rate=0.09, \n",
    "                        subsample=.8,\n",
    "                        min_child_weight=6,\n",
    "                        colsample_bytree=.6,\n",
    "                        scale_pos_weight=1.6,\n",
    "                        gamma=9,\n",
    "                        seed=100,\n",
    "                        reg_alpha=8,\n",
    "                        reg_lambda=1.3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Flod: 0\n",
      "          0\n",
      "0  0.042405\n",
      "1  0.047065\n",
      "2  0.040129\n",
      "3  0.029114\n",
      "4  0.064558\n",
      "5  0.027070\n",
      "6  0.048909\n",
      "7  0.034641\n",
      "8  0.039920\n",
      "9  0.047823\n",
      "Gini: 0.306727985249\n",
      "\n",
      "Flod: 1\n",
      "          0\n",
      "0  0.062031\n",
      "1  0.034222\n",
      "2  0.040529\n",
      "3  0.040472\n",
      "4  0.057611\n",
      "5  0.027468\n",
      "6  0.110799\n",
      "7  0.061612\n",
      "8  0.036548\n",
      "9  0.030752\n",
      "Gini: 0.254182254043\n",
      "\n",
      "Flod: 2\n",
      "          0\n",
      "0  0.054406\n",
      "1  0.044603\n",
      "2  0.028314\n",
      "3  0.021377\n",
      "4  0.058505\n",
      "5  0.042860\n",
      "6  0.051326\n",
      "7  0.060766\n",
      "8  0.055152\n",
      "9  0.047511\n",
      "Gini: 0.292281188352\n",
      "\n",
      "Flod: 3\n",
      "          0\n",
      "0  0.026864\n",
      "1  0.048234\n",
      "2  0.079623\n",
      "3  0.046678\n",
      "4  0.058819\n",
      "5  0.081230\n",
      "6  0.045703\n",
      "7  0.039765\n",
      "8  0.036289\n",
      "9  0.060680\n",
      "Gini: 0.291305098598\n",
      "\n",
      "Flod: 4\n",
      "          0\n",
      "0  0.047827\n",
      "1  0.052555\n",
      "2  0.029645\n",
      "3  0.040330\n",
      "4  0.028396\n",
      "5  0.036289\n",
      "6  0.042943\n",
      "7  0.040074\n",
      "8  0.057430\n",
      "9  0.059822\n",
      "Gini: 0.28443306161\n",
      "\n",
      "Flod: 5\n",
      "          0\n",
      "0  0.024204\n",
      "1  0.095924\n",
      "2  0.104435\n",
      "3  0.074165\n",
      "4  0.071915\n",
      "5  0.037372\n",
      "6  0.119283\n",
      "7  0.075220\n",
      "8  0.034999\n",
      "9  0.038627\n",
      "Gini: 0.294187703218\n",
      "\n",
      "Flod: 6\n",
      "          0\n",
      "0  0.090417\n",
      "1  0.040472\n",
      "2  0.099919\n",
      "3  0.086653\n",
      "4  0.031037\n",
      "5  0.020106\n",
      "6  0.127092\n",
      "7  0.071423\n",
      "8  0.037759\n",
      "9  0.054767\n",
      "Gini: 0.282185756409\n",
      "\n",
      "Flod: 7\n",
      "          0\n",
      "0  0.063056\n",
      "1  0.041693\n",
      "2  0.045763\n",
      "3  0.041578\n",
      "4  0.054408\n",
      "5  0.048993\n",
      "6  0.035622\n",
      "7  0.055476\n",
      "8  0.046831\n",
      "9  0.046918\n",
      "Gini: 0.292329186367\n",
      "\n",
      "Flod: 8\n",
      "          0\n",
      "0  0.071999\n",
      "1  0.077683\n",
      "2  0.029124\n",
      "3  0.031555\n",
      "4  0.040452\n",
      "5  0.064700\n",
      "6  0.068666\n",
      "7  0.048330\n",
      "8  0.058751\n",
      "9  0.028910\n",
      "Gini: 0.276699663122\n",
      "\n",
      "Flod: 9\n",
      "          0\n",
      "0  0.027224\n",
      "1  0.033326\n",
      "2  0.043890\n",
      "3  0.023086\n",
      "4  0.032790\n",
      "5  0.023428\n",
      "6  0.066012\n",
      "7  0.050659\n",
      "8  0.035338\n",
      "9  0.029983\n",
      "Gini: 0.275369547033\n",
      "Gini for full training set: 0.284848111394\n"
     ]
    }
   ],
   "source": [
    "y_test_pred=0#0.277\n",
    "y_valid_pred=0*y\n",
    "fit_model=[]\n",
    "for i,(train_index,test_index) in enumerate(kf.split(X,y)):\n",
    "    y_train,y_valid=y.iloc[train_index],y.iloc[test_index]\n",
    "    x_train,x_valid=X.iloc[train_index],X.iloc[test_index]\n",
    "    print('\\nFlod:',i)\n",
    "    fitted_model=model.fit(x_train,y_train)\n",
    "    pred= fitted_model.predict_proba(x_valid)[:,1]\n",
    "    y_valid_pred.iloc[test_index]=pred\n",
    "    print(pd.DataFrame(pred).head(10))\n",
    "    print('Gini:',eval_gini(y_valid,pred))\n",
    "    y_test_pred+=fitted_model.predict_proba(test)[:,1]\n",
    "    fit_model.append(fitted_model)\n",
    "y_test_pred/=K\n",
    "print('Gini for full training set:',eval_gini(y,y_valid_pred))\n",
    "sub=pd.DataFrame({'id':test_id,'target':y_test_pred})\n",
    "# sub.to_csv(r'.\\KFlod_submission.csv',float_format='%.4f',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "sub.to_csv(r'.\\KFlod_submission.csv',float_format='%.6f',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
